import sqlite3
import GV

# region BlockDb
BlockDbFileName = GV.BlockDbFileName
dbConn = sqlite3.connect(BlockDbFileName)
print("Opened BlockDb successfully")

#创建表Block
strSql = "CREATE TABLE [Block](\
  [B_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 0, \
  [B_Filename] TEXT NOT NULL UNIQUE, \
  [B_BlockID] INT64 DEFAULT 0, \
  [B_BlockHash] TEXT, \
  [B_Timestamp] FLOAT DEFAULT 0, \
  [B_PrevBlockID] INT64 DEFAULT 0, \
  [B_PrevBlockHash] TEXT, \
  [B_Confirmed] INTEGER DEFAULT 0, \
  [B_Forked] INTEGER DEFAULT 0);"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [Block] Successfully.")

#创建表MiningDifficulty
strSql = "CREATE TABLE [MiningDifficulty](\
  [MD_Phase] INT PRIMARY KEY, \
  [MD_MiningDifficulty] INT) WITHOUT ROWID;"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [MiningDifficulty] Successfully.")

# 创建表AccountInfo
strSql = "CREATE TABLE [AccountInfo](\
  [AI_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [AI_AccountAddress] TEXT UNIQUE, \
  [AI_BlockID] INTEGER DEFAULT 0, \
  [AI_TokenCount] FLOAT DEFAULT 0);"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [AccountInfo] Successfully.")

# 创建表TransactionInfo
strSql = "CREATE TABLE [TransactionInfo](\
  [TI_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [TI_TranHash] TEXT , \
  [TI_B_RecordID] INTEGER REFERENCES [Block]([B_RecordID]));"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [TransactionInfo] Successfully.")

# close
dbConn.close()
#endregion

#region ServerUseDb
ServerDbFilename = GV.ServerDbFilename
dbConn = sqlite3.connect(ServerDbFilename)
print("Opened ServerUseDb successfully")

strSql = "CREATE TABLE [ServerUseBlockList](\
  [SUBL_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT,\
  [SUBL_Filename] TEXT, \
  [SUBL_Type] INTEGER DEFAULT 1, \
  [SUBL_Hash] TEXT, \
  [SUBL_BlockID] INTEGER, \
  [SUBL_ProviderIP] VARCHAR(200), \
  [SUBL_ProviderPort] INTEGER, \
  [SUBL_Timestampe] FLOAT, \
  UNIQUE([SUBL_Filename], [SUBL_Hash], [SUBL_ProviderIP], [SUBL_ProviderPort]));"
c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
dbConn.commit()
print("CREATE TABLE [ServerUseBlockList] Successfully.")

dbConn.close()
#endregion

#region ClientUseDb
ClientDbFilename = GV.ClientDbFilename
dbConn = sqlite3.connect(ClientDbFilename)
print("Opened ClientUseDb successfully")
strSql = "CREATE TABLE [ClientUseBlockListFromServer](\
  [CUBLFS_RecordID] INTEGER PRIMARY KEY AUTOINCREMENT, \
  [CUBLFS_Filename] TEXT, \
  [CUBLFS_Type] INTEGER DEFAULT 1, \
  [CUBLFS_Hash] TEXT, \
  [CUBLFS_BlockID] INTEGER, \
  [CUBLFS_ProviderIP] VARCHAR(200), \
  [CUBLFS_ProviderPort] INTEGER, \
  [CUBLFS_Timestampe] FLOAT, \
  UNIQUE([CUBLFS_Filename], [CUBLFS_Hash], [CUBLFS_ProviderIP], [CUBLFS_ProviderPort]));"

c = dbConn.cursor()
c.execute(strSql)
dbConn.commit()
print("CREATE TABLE [ClientUseBlockListFromServer] Successfully.")
dbConn.close()
#endregion


